--region [dbo].[AddComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddComment]
GO

CREATE PROCEDURE [dbo].[AddComment]
	@HostUserId int,
	@Body ntext,
	@FloorNo int,
	@ReplyFloorNo int,
	@PostUserId int,
	@PostUserName nvarchar(50),
	@Ip nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime,
	@IsRead int
AS

INSERT INTO [Comment] (
	[HostUserId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime],
	[IsRead]
) VALUES (
	@HostUserId,
	@Body,
	@FloorNo,
	@ReplyFloorNo,
	@PostUserId,
	@PostUserName,
	@Ip,
	@PostTime,
	@UpdateTime,
	@IsRead
)

GO
--endregion


--region [dbo].[UpdateComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateComment]
GO

CREATE PROCEDURE [dbo].[UpdateComment]
	@Id int,
	@HostUserId int,
	@Body ntext,
	@FloorNo int,
	@ReplyFloorNo int,
	@PostUserId int,
	@PostUserName nvarchar(50),
	@Ip nvarchar(50),
	@PostTime datetime,
	@UpdateTime datetime,
	@IsRead int
AS

UPDATE [Comment] SET
	[HostUserId] = @HostUserId,
	[Body] = @Body,
	[FloorNo] = @FloorNo,
	[ReplyFloorNo] = @ReplyFloorNo,
	[PostUserId] = @PostUserId,
	[PostUserName] = @PostUserName,
	[Ip] = @Ip,
	[PostTime] = @PostTime,
	[UpdateTime] = @UpdateTime,
	[IsRead] = @IsRead
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateCommentState]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateCommentState]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateCommentState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateCommentState]
GO

CREATE PROCEDURE [dbo].[UpdateCommentState]
	@uid int,
	@state int
AS

UPDATE [Comment] SET
	[IsRead] = @state
WHERE
	[HostUserId] = @uid

GO
--endregion

--region [dbo].[DelComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelComment]
GO

CREATE PROCEDURE [dbo].[DelComment]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Comment]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetComment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetComment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetComment]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetComment]
GO

CREATE PROCEDURE [dbo].[GetComment]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[HostUserId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	[Ip],
	[PostTime],
	[UpdateTime]
FROM
	[Comment]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetCommentList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCommentList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCommentList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCommentList]
GO

CREATE PROCEDURE [dbo].[GetCommentList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Comment]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Comment].[Id],
	[HostUserId],
	[Body],
	[FloorNo],
	[ReplyFloorNo],
	[PostUserId],
	[PostUserName],
	''PostAvatar'' = [Avatar],
	[Ip],
	[PostTime],
	[Comment].[UpdateTime],
	[IsRead]
FROM
	[Comment], [UserInfo]
WHERE
	[Comment].[PostUserId] = [UserInfo].[Id]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Comment].[Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [Comment] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


-region [dbo].[GetCommentReplyListByUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetCommentReplyListByUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetCommentReplyListByUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetCommentReplyListByUser]
GO

CREATE PROCEDURE [dbo].[GetCommentReplyListByUser]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Comment]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Comment].[Id],
	[HostUserId],
	[Body],
	[PostUserId],
	[PostUserName],
	''PostAvatar'' = [Avatar],
	[PostTime],
	[Comment].[UpdateTime],
    [ParentId],
    ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
INTO ##t2
FROM
	[Comment], [UserInfo]
WHERE
	[Comment].[PostUserId] = [UserInfo].[Id]
AND
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + @WhereCondition
END

EXEC sp_executesql @SQL


SELECT *
FROM  ##t2
 AS tblTmp  
WHERE tblTmp.SerialNumber > STR((@PageIndex-1)*@PageSize) AND tblTmp.SerialNumber <= STR(@PageIndex*@PageSize)
 AND [PostTime] = (SELECT MAX([PostTime]) FROM ##t2 WHERE [PostUserName] = tblTmp.[PostUserName] AND [ParentId] = tblTmp.[ParentId])


--endregion
